﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ObligatorioBD.MerObjects;
using System.Data.SqlClient;

namespace ObligatorioBD.Logic
{
    class MerBuilder
    {
        SqlConnection cnx;
        public MerBuilder(SqlConnection cnx)
        {
            this.cnx = cnx;
        }
        public Mer GetMer(int merId)
        {
            Mer m = null;
            SqlCommand cmd = new SqlCommand("SELECT * FROM Mer WHERE MerId = @MerId", this.cnx);
            cmd.Parameters.Add("@MerId",System.Data.SqlDbType.Int);
            cmd.Parameters["@MerId"].Value = merId;
            cnx.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                m = new Mer(Convert.ToInt32(dr["MerId"]),dr["Name"].ToString(), dr["Owner"].ToString());
            }
            cnx.Close();
            AddEntities(ref m);
            AddRelations(ref m);
            return m;
            
        }
        private void AddEntities(ref Mer mer)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Entity WHERE MerId = @MerId", this.cnx);
            cmd.Parameters.Add("@MerId", System.Data.SqlDbType.Int);
            cmd.Parameters["@MerId"].Value = mer.ObjectId;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            MerDataSet ds = new MerDataSet();
            da.Fill(ds.Entity);
            Entity e;
            IAtributeObject a;
            foreach (MerDataSet.EntityRow row in ds.Entity.Rows)
            {
                e = new Entity(row.Name, row.isWeak);
                e.ObjectId = row.EntityId;
                a = e;
                AddAtributes(ref a, e.ObjectId);
                mer.AddEntity(e);
            }
        }
        private void AddRelations(ref Mer mer)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Relations WHERE MerId = @MerId", this.cnx);
            cmd.Parameters.Add("@MerId", System.Data.SqlDbType.Int);
            cmd.Parameters["@MerId"].Value = mer.ObjectId;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            MerDataSet ds = new MerDataSet();
            da.Fill(ds.Relation);
            Relationship r;
            IAtributeObject a;
            foreach (MerDataSet.RelationRow row in ds.Relation.Rows)
            {
                r = new Relationship(row.Name);
                r.ObjectId = row.RelationId;
                a = r;
                AddAtributes(ref a, r.ObjectId);
                AddRelationEntity(ref r, mer);
                mer.AddRelation(r);
            }
        }
        private void AddRelationEntity(ref Relationship r, Mer mer)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM RelationEntity WHERE RelationId= @RelId", this.cnx);
            cmd.Parameters.Add("@RelId", System.Data.SqlDbType.Int);
            cmd.Parameters["@RelId"].Value = r.ObjectId;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            MerDataSet ds = new MerDataSet();
            da.Fill(ds.CardinalEntity);
            foreach (MerDataSet.CardinalEntityRow row in ds.CardinalEntity.Rows)
            {
                r.AddEntity(mer.GetEntityById(row.EntityId), row.Cardinality);
            }
        }
        private void AddAtributes(ref IAtributeObject obj, int objId)
        {
            string cmdText = "";
            if (obj is Entity)
            {
                cmdText = "SELECT a.*, ae.isKey, ae.quantity FROM Atributes a LEFT JOIN EntityAtributes ae ";
                cmdText += "ON ae.AtributeId = a.AtributeId WHERE a.AtributeId IN ";
                cmdText += "(SELECT AtributeId FROM EntityAtributes WHERE EntityId = @Id)";
            }
            else if (obj is Relationship)
            {
                cmdText = "SELECT a.*, ae.isKey, ae.quantity FROM Atributes a LEFT JOIN RelationAtributes ae ";
                cmdText += "ON ae.AtributeId = a.AtributeId WHERE a.AtributeId IN ";
                cmdText += "(SELECT AtributeId FROM RelationAtributes WHERE RelationId = @Id)";
            }
            else if (obj is Atribute)
            {
                cmdText = "SELECT a.*, ca.isKey, ca.quantity FROM Atributes a LEFT JOIN CompositeAtribute ca ";
                cmdText += "ON ca.CompositeAtributeId = a.AtributeId WHERE a.AtributeId IN ";
                cmdText += "(SELECT AtributeId FROM CompositeAtribute WHERE CompositeAtributeId = @Id)";
            }
            SqlCommand cmd = new SqlCommand(cmdText, this.cnx);
            cmd.Parameters.Add("@Id", System.Data.SqlDbType.Int);
            cmd.Parameters["@Id"].Value = objId;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            MerDataSet ds = new MerDataSet();
            da.Fill(ds.Atributes);
            Atribute a;
            IAtributeObject ia;
            foreach (MerDataSet.AtributesRow row in ds.Atributes.Rows)
            {
                Atribute.AtributeType type = Atribute.GetType(row.Type);
                bool isKey;
                if (obj is Atribute)
                {
                    isKey = false;
                }
                else
                {
                    isKey = row.isKey;
                }
                int quantity = 1;
                try
                {
                    quantity = row.Quantity;
                }
                catch { }

                a = new Atribute(row.Name, type, quantity, isKey);
                a.ObjectId = row.AtributeId;
                obj.AddAtribute(a);
                if (a.Type == Atribute.AtributeType.CompositeType)
                {
                    ia = a;
                    AddAtributes(ref ia, a.ObjectId);
                }
            }
        }


    }
}
